package com.bsoft.gol.hbs.dao;

import com.bsoft.gol.hbs.utils.CommonUtils;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.spring.boot.annotation.SsdevDao;
import ctd.util.annotation.RpcService;
import ctd.util.converter.ConversionUtils;
import hcn.base.BaseDicContrast;
import hcn.base.drug.qo.QueryLocaldicQo;
import hcn.base.drug.vo.DicContrastVo;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.atag.dao.util.BaseDaoImpl;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import pcn.vo.base.QueryDicContrastListVo;

/**
 * Created by pangxl on 2019/12/19.
 */
@SsdevDao(serviceId = "dicContrastDAOService")
public abstract class DicContrastDAO extends BaseDaoImpl<BaseDicContrast> {
    public DicContrastDAO() {
        this.setKeyField("contrastId");
    }

    @RpcService
    @DAOMethod(sql = " from BaseDicContrast where  itemId=:itemId and deleteStatus=0 ")
    public abstract BaseDicContrast getBaseDicContrastByTeamId(@DAOParam("itemId") String itemId);

    @RpcService
    @DAOMethod(sql = "delete from BaseDicContrast where  baseItemId=:baseItemId ")
    public abstract  void deleteBaseDicContrastByItemId( @DAOParam("baseItemId") String baseItemId);

    @RpcService
    public List<DicContrastVo> queryStandardDicList(final QueryLocaldicQo qo) {
        StringBuffer hsql = new StringBuffer();
        hsql.append("SELECT b.dic_name as dicName, b.dic_code dicCode, b.dic_id dicId, i.item_id itemId, i.item_name itemName, i.item_code itemCode, i.status status " +
                "FROM base_dictionary b RIGHT JOIN base_dic_item i ON b.dic_id = i.dic_id WHERE b.object_type = 1 ");
        Map<String,Object> params = new HashMap<>();
        if(CommonUtils.isNotEmpty(qo.getDicCode())){
            hsql.append(" and b.dic_code=:dicCode");
            params.put("dicCode",qo.getDicCode());
        }
        if(CommonUtils.isNotEmpty(qo.getItemName())){
            hsql.append(" and i.item_name like :itemName ");
            params.put("itemName","%"+qo.getItemName()+"%");
        }
        hsql.append("  order by i.sort asc ");
        return super.findListBySql(hsql.toString(),params,DicContrastVo.class,qo.getPageNo(),qo.getPageSize());
        /*
        HibernateStatelessResultAction<List<DicContrastVo>> action = new AbstractHibernateStatelessResultAction<List<DicContrastVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                hsql.append("SELECT b.dic_name as dicName, b.dic_code dicCode, b.dic_id dicId, i.item_id itemId, i.item_name itemName, i.item_code itemCode, i.status status " +
                        "FROM base_dictionary b RIGHT JOIN base_dic_item i ON b.dic_id = i.dic_id WHERE b.object_type = 1 ");
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    hsql.append(" and b.dic_code=:dicCode");
                }
                if(CommonUtils.isNotEmpty(qo.getItemName())){
                    hsql.append(" and i.item_name like '%"+qo.getItemName()+"%' ");
                }
                hsql.append("  order by i.sort asc ");

                Query query = ss.createSQLQuery(hsql.toString());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    query.setParameter("dicCode", qo.getDicCode());
                }

                int first = qo.getPageNo() > 0 ? (qo.getPageNo() - 1) * qo.getPageSize() : 0;
                query.setFirstResult(first);
                query.setMaxResults(qo.getPageSize());
                List<Map> dbList = query.list();
                List<DicContrastVo> ls = new ArrayList<>();
                for (Map map : dbList) {
                    ls.add(ConversionUtils.convert(map, DicContrastVo.class));
                }
                setResult(ls);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();*/
    }

    @RpcService
    public BigInteger queryStandardDicListCount(final QueryLocaldicQo qo ) {
        StringBuffer hsql = new StringBuffer();
        hsql.append("SELECT count(1) FROM base_dictionary b RIGHT JOIN base_dic_item i ON b.dic_id = i.dic_id WHERE b.object_type = 1");

        Map<String,Object> params = new HashMap<>();
        if(CommonUtils.isNotEmpty(qo.getDicCode())){
            hsql.append(" and b.dic_code=:dicCode");
            params.put("dicCode",qo.getDicCode());
        }
        if(CommonUtils.isNotEmpty(qo.getItemName())){
            hsql.append(" and i.item_name like :itemName ");
            params.put("itemName","%"+qo.getItemName()+"%");
        }
        return BigInteger.valueOf(super.findForLongBySql(hsql.toString(),params));
    /*
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                hsql.append("SELECT count(1) FROM base_dictionary b RIGHT JOIN base_dic_item i ON b.dic_id = i.dic_id WHERE b.object_type = 1");
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    hsql.append(" and b.dic_code=:dicCode");
                }
                if(CommonUtils.isNotEmpty(qo.getItemName())){
                    hsql.append(" and i.item_name like '%"+qo.getItemName()+"%' ");
                }
                Query query = ss.createSQLQuery(hsql.toString());
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    query.setParameter("dicCode", qo.getDicCode());
                }

                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();*/
    }

    @RpcService
    public List<DicContrastVo> queryLocalDicList(QueryLocaldicQo qo, List<String> orgList) {
        StringBuffer hsql = new StringBuffer();
        hsql.append("SELECT i.sort,i.item_id itemId, i.item_name itemName, i.item_code itemCode, i.status, b.dic_name dicName, b.dic_code dicCode, b.dic_id dicId, c.contrast_id contrastId,t.item_code as scode, t.item_name as sname");
        hsql.append(" from base_dic_item i LEFT JOIN base_dictionary b  ON b.dic_id = i.dic_id");
        hsql.append(" LEFT JOIN base_dic_contrast c on c.item_id=i.item_id and i.dic_id=c.dic_id and c.delete_status=0");
        hsql.append( " LEFT JOIN base_dic_item t on t.item_id=c.base_item_id where b.object_type=2");
        Map<String,Object> params = new HashMap<>();
        if(CommonUtils.isNotEmpty(qo.getDicCode())){
            hsql.append(" and  b.dic_code =:dicCode ");
            params.put("dicCode",qo.getDicCode());
        }
        if(CommonUtils.isNotEmpty(qo.getItemName())){
            hsql.append(" and i.item_name like :itemName ");
            params.put("itemName","%"+qo.getItemName()+"%");
        }
        if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "0".equals(qo.getIsMatch())){
            hsql.append(" and c.contrast_id is null");
        }
        if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "1".equals(qo.getIsMatch())){
            hsql.append(" and c.contrast_id is not null");
        }
        if(CommonUtils.isNotEmpty(qo.getObjectId())){
            hsql.append(" and  b.object_id =:objectId ");
            params.put("objectId",qo.getObjectId().trim());
        }
        if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
            hsql.append(" and b.object_id in (:orgList)");
            params.put("orgList",orgList);
        }
        hsql.append(" order by i.sort asc");
        return super.findListBySql(hsql.toString(),params,DicContrastVo.class,qo.getPageNo(),qo.getPageSize().intValue());
        /*
        HibernateStatelessResultAction<List<DicContrastVo>> action = new AbstractHibernateStatelessResultAction<List<DicContrastVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                hsql.append("SELECT i.sort,i.item_id itemId, i.item_name itemName, i.item_code itemCode, i.status, b.dic_name dicName, b.dic_code dicCode, b.dic_id dicId, c.contrast_id contrastId,t.item_code as scode, t.item_name as sname");

//                if(CommonUtils.isNotEmpty(qo.getDicCode()) && "drug_frequency".equals(qo.getDicCode())){
//                    hsql.append(" ,ra.rate_day as rateDay,ra.rate_times as rateTimes,ra.rate_his_time as rateHisTime,ra.rate_local_time as rateLocalTime,ra.rate_cycle as rateCycle,ra.rate_remarks as remarks");
//                }
                hsql.append(" from base_dic_item i LEFT JOIN base_dictionary b  ON b.dic_id = i.dic_id");
//                hsql.append( " LEFT JOIN base_orgmain o on o.orgid=b.object_id");
                hsql.append(" LEFT JOIN base_dic_contrast c on c.item_id=i.item_id and i.dic_id=c.dic_id and c.delete_status=0");

//                if(CommonUtils.isNotEmpty(qo.getDicCode()) && "drug_frequency".equals(qo.getDicCode())){
//                    hsql.append(" LEFT JOIN medication_use_rate ra on ra.dic_item_id=i.item_id ");
//                }
//                if(CommonUtils.isNotEmpty(tenantId)){
//                    hsql.append( " LEFT JOIN base_orgcloudmem m on m.orgid=b.object_id ");
//                }
                hsql.append( " LEFT JOIN base_dic_item t on t.item_id=c.base_item_id where b.object_type=2");
//                if(CommonUtils.isNotEmpty(tenantId)){
//                    hsql.append( "  and m.cloudId='"+tenantId+"' ");
//                }
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    hsql.append(" and  b.dic_code ='"+qo.getDicCode()+"' ");
                }
                if(CommonUtils.isNotEmpty(qo.getItemName())){
                    hsql.append(" and i.item_name like '%"+qo.getItemName()+"%' ");
                }
                if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "0".equals(qo.getIsMatch())){
                    hsql.append(" and c.contrast_id is null");
                }
                if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "1".equals(qo.getIsMatch())){
                    hsql.append(" and c.contrast_id is not null");
                }
                if(CommonUtils.isNotEmpty(qo.getObjectId())){
                    hsql.append(" and  b.object_id ='"+qo.getObjectId().trim()+"' ");
                }
                if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
                    hsql.append(" and b.object_id in (:orgList)");
                }
//                if(CommonUtils.isNotEmpty(areaList)&&areaList.size()>0){
//                   hsql.append(" and substr(o.area,1,6) in (:areaList)");
//                }
                hsql.append(" order by i.sort asc");
                Query query = ss.createSQLQuery(hsql.toString());
                if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
                    query.setParameterList("orgList",orgList);
                }
//                if(CommonUtils.isNotEmpty(areaList)&&areaList.size()>0){
//                    query.setParameterList("areaList",areaList);
//                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                int first = qo.getPageNo() > 0 ? (qo.getPageNo() - 1) * qo.getPageSize() : 0;
                query.setFirstResult(first);
                query.setMaxResults(qo.getPageSize());
                List<Map> dbList = query.list();
                List<DicContrastVo> ls = new ArrayList<>();
                for (Map map : dbList) {
                    ls.add(ConversionUtils.convert(map, DicContrastVo.class));
                }
                setResult(ls);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();*/
    }

    @RpcService
    public BigInteger queryLocalDicListCount(QueryLocaldicQo qo, final List<String> orgList) {
        StringBuffer hsql = new StringBuffer();
        hsql.append("SELECT count(1) ");
        hsql.append(" from base_dic_item i LEFT JOIN base_dictionary b  ON b.dic_id = i.dic_id");
        hsql.append( " LEFT JOIN base_dic_contrast c on c.item_id=i.item_id and i.dic_id=c.dic_id and c.delete_status=0 where b.object_type=2");
        Map<String,Object> params = new HashMap<>();
        if(CommonUtils.isNotEmpty(qo.getDicCode())){
            hsql.append(" and  b.dic_code =:dicCode ");
            params.put("dicCode",qo.getDicCode());
        }
        if(CommonUtils.isNotEmpty(qo.getItemName())){
            hsql.append(" and i.item_name like :itemName ");
            params.put("itemName","%"+qo.getItemName()+"%");
        }
        if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "0".equals(qo.getIsMatch())){
            hsql.append(" and c.contrast_id is null");
        }
        if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "1".equals(qo.getIsMatch())){
            hsql.append(" and c.contrast_id is not null");
        }
        if(CommonUtils.isNotEmpty(qo.getObjectId())){
            hsql.append(" and  b.object_id =:objectId ");
            params.put("objectId",qo.getObjectId().trim());
        }
        if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
            hsql.append(" and b.object_id in (:orgList)");
            params.put("orgList",orgList);
        }

       return BigInteger.valueOf(this.findForLongBySql(hsql.toString(),params));

       /* HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                hsql.append("SELECT count(1) ");
                hsql.append(" from base_dic_item i LEFT JOIN base_dictionary b  ON b.dic_id = i.dic_id");
                hsql.append( " LEFT JOIN base_dic_contrast c on c.item_id=i.item_id and i.dic_id=c.dic_id and c.delete_status=0 where b.object_type=2");
                if(CommonUtils.isNotEmpty(qo.getDicCode())){
                    hsql.append(" and  b.dic_code ='"+qo.getDicCode()+"' ");
                }
                if(CommonUtils.isNotEmpty(qo.getItemName())){
                    hsql.append(" and i.item_name like '%"+qo.getItemName()+"%' ");
                }
                if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "0".equals(qo.getIsMatch())){
                    hsql.append(" and c.contrast_id is null");
                }
                if(CommonUtils.isNotEmpty(qo.getIsMatch())&& "1".equals(qo.getIsMatch())){
                    hsql.append(" and c.contrast_id is not null");
                }
                if(CommonUtils.isNotEmpty(qo.getObjectId())){
                    hsql.append(" and  b.object_id ='"+qo.getObjectId().trim()+"' ");
                }
                if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
                    hsql.append(" and b.object_id in (:orgList)");
                }

                Query query = ss.createSQLQuery(hsql.toString());
                if(CommonUtils.isNotEmpty(orgList)&&orgList.size()>0){
                    query.setParameterList("orgList",orgList);
                }

                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();*/
    }



  //获取权限 机构或区县
    @RpcService
    public List<String> queryUserRegion(final String userId,final String unitType) {
        HibernateStatelessResultAction<List<String>> action = new AbstractHibernateStatelessResultAction<List<String>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                if("area".equals(unitType)){
                    hsql.append("SELECT substr(unitObjId,1,6) from BaseUserManageUnit");
                    hsql.append(" where userId=:userId and unitType=:unitType");
                    hsql.append(" GROUP BY substr(unitObjId,1,6)");
                }else{
                    hsql.append("SELECT unitObjId from BaseUserManageUnit");
                    hsql.append(" where userId=:userId and unitType=:unitType");
                }
                Query query = ss.createQuery(hsql.toString());
                query.setParameter("userId", userId);
                query.setParameter("unitType", unitType);
                List<String> map = ( List<String>) query.list();
                setResult(map);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



    /**
     * 根据当前登录人权限获取机构列表
     * @deprecated see hcn-web hcn.base.service.OrganizationService#queryCurrentAuthOrgList
     */
//    @RpcService
//    public List<Organization> queryOrgListByunit(final  String manageUnit, final UserRoleToken urt, final List<String> orgList, final List<String> areaList, final String orgName) {
//        HibernateStatelessResultAction<List<Organization>> action = new AbstractHibernateStatelessResultAction<List<Organization>>() {
//            @Override
//            public void execute(StatelessSession ss) throws Exception {
//                StringBuffer hsql = new StringBuffer();
//                hsql.append("SELECT r.* FROM base_orgmain r LEFT JOIN base_orgcloudmem m on r.orgId=m.orgId where r.status = 1");
//                if("1".equals(manageUnit) &&!"admin".equals(urt.getRoleId())){
//                    hsql.append(" and m.cloudId='"+urt.getTenantId()+"'");
//                }else if("2".equals(manageUnit)){
//                    hsql.append(" and r.orgId in (:orgList)");
//                }else if("4".equals(manageUnit)){
//                    hsql.append(" and substr(r.area,1,6) in(:areaList)");
//                }
//                if(CommonUtils.isNotEmpty(orgName)){
//                    hsql.append(" and r.fullName like '%"+orgName+"%'");
//                }
//                Query query = ss.createSQLQuery(hsql.toString());
//                if("2".equals(manageUnit)) {
//                    query.setParameterList("orgList",orgList);
//                }else if("4".equals(manageUnit)) {
//                    query.setParameterList("areaList",areaList);
//                }
//                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
//                List<Map> dbList = query.list();
//                List<Organization> ls = new ArrayList<>();
//                for (Map map : dbList) {
//                    ls.add(ConversionUtils.convert(map, Organization.class));
//                }
//                setResult(ls);
//            }
//        };
//        HibernateSessionTemplate.instance().execute(action);
//        return action.getResult();
//    }

    /**
     * @description: 查询字典对照的值
     * @author: chenJij@bsoft.com.cn
     * @date: 2021/9/16 16:02
     */
    @RpcService
    public List<QueryDicContrastListVo> queryDicContrastList(final String dicCode, final String objectId) {
        HibernateStatelessResultAction<List<QueryDicContrastListVo>> action = new AbstractHibernateStatelessResultAction<List<QueryDicContrastListVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();
                hsql.append("SELECT " +
                        " base.item_code baseItemCode, " +
                        " his.item_code hisItemCode, " +
                        " b.object_type objectType, " +
                        " b.object_id objectId " +
                        " FROM " +
                        " base_dic_contrast a, " +
                        " base_dictionary b, " +
                        " base_dic_item base, " +
                        " base_dic_item his " +
                        " WHERE " +
                        " a.dic_id = b.dic_id " +
                        "AND a.base_item_id = base.item_id " +
                        "AND a.item_id = his.item_id " +
                        "AND b.dic_code =:dicCode " +
                        "AND b.object_type = '2' and b.object_id=:objectId ");
                Query query = ss.createSQLQuery(hsql.toString());
                query.setParameter("dicCode", dicCode);
                query.setParameter("objectId", objectId);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> dbList = query.list();
                List<QueryDicContrastListVo> ls = new ArrayList<>();
                for (Map map : dbList) {
                    ls.add(ConversionUtils.convert(map, QueryDicContrastListVo.class));
                }
                setResult(ls);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

}
